Database Design

Iquea Commerce uses MySQL 8.x as the relational database with JPA/Hibernate for ORM. The schema is automatically generated from JPA entities and populated with initial data.

Database Configuration

application.properties

spring.application.name=Iquea

# Server Configuration
server.port=8080

# MySQL Database Connection
spring.datasource.url=jdbc:mysql://localhost:3306/apiIquea?createDatabaseIfNotExist=true
spring.datasource.username=${DB_USERNAME:root}
spring.datasource.password=${DB_PASSWORD:MCS_47_2006}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

# Data Initialization
spring.sql.init.mode=always
spring.jpa.defer-datasource-initialization=true
spring.sql.init.data-locations=classpath:data.sql

# Naming Strategy - Respect entity table names
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Key configurations:

createDatabaseIfNotExist=true - Auto-creates database
ddl-auto=update - Creates/updates tables without data loss
show-sql=true - Logs SQL statements (disable in production)
defer-datasource-initialization=true - Runs data.sql after schema creation
PhysicalNamingStrategyStandardImpl - Uses exact entity table names

Environment variables:

export DB_USERNAME=your_username
export DB_PASSWORD=your_password

Database Schema

Entity-Relationship Diagram

Table Definitions

Usuario Table

Stores user accounts (customers and administrators).

CREATE TABLE Usuario (
    usuario_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    Email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    rol VARCHAR(20) NOT NULL DEFAULT 'CLIENTE',
    fecha_nacimiento DATE NOT NULL,
    direccion_envio TEXT NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    apellidos VARCHAR(150) NOT NULL,
    activo BOOLEAN DEFAULT TRUE,
    
    INDEX idx_email (Email),
    INDEX idx_rol (rol)
);

Key constraints:

Email - Unique constraint enforced at database level
rol - Enum stored as VARCHAR (‘ADMIN’, ‘CLIENTE’)
activo - Soft delete flag

Mapped from entity:

@Entity
@Table(name = "Usuario")
public class Usuario {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long usuario_id;
    
    @Embedded
    @AttributeOverride(name = "value", 
                      column = @Column(name = "Email", 
                                      nullable = false, 
                                      unique = true))
    private Email email;
    
    @Enumerated(EnumType.STRING)
    @Column(name = "rol", nullable = false)
    private RolUsuario rol = RolUsuario.CLIENTE;
    // ...
}

categorias Table

Product categories (Salón, Dormitorio, Cocina, etc.).

CREATE TABLE categorias (
    categoria_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    Slug VARCHAR(155) NOT NULL,
    
    UNIQUE INDEX idx_slug (Slug)
);

Initial data:

INSERT IGNORE INTO categorias (categoria_id, nombre, Slug) VALUES
(1, 'Salón',        'salon'),
(2, 'Dormitorio',   'dormitorio'),
(3, 'Cocina',       'cocina'),
(4, 'Baño',         'bano'),
(5, 'Oficina',      'oficina'),
(6, 'Exterior',     'exterior');

Producto Table

Furniture products with embedded value objects (Precio, Dimensiones).

CREATE TABLE Producto (
    producto_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    categoria_id BIGINT NOT NULL,
    nombre VARCHAR(155) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    
    -- Embedded: Precio value object
    Cantidad DECIMAL(19,2) NOT NULL,
    Moneda VARCHAR(3) NOT NULL,
    
    es_destacado BOOLEAN DEFAULT FALSE,
    
    -- Embedded: Dimensiones value object
    Alto DOUBLE NOT NULL,
    Ancho DOUBLE NOT NULL,
    Profundidad DOUBLE NOT NULL,
    
    descripcion TEXT,
    stock INT NOT NULL DEFAULT 0,
    imagen_url VARCHAR(255),
    
    FOREIGN KEY (categoria_id) REFERENCES categorias(categoria_id),
    UNIQUE INDEX idx_sku (sku),
    INDEX idx_categoria (categoria_id),
    INDEX idx_destacado (es_destacado)
);

Value object mapping:

Precio → Cantidad + Moneda columns
Dimensiones → Alto + Ancho + Profundidad columns

Sample products from data.sql:

INSERT IGNORE INTO Producto (
    producto_id, categoria_id, nombre, sku, 
    Cantidad, Moneda, es_destacado, 
    Alto, Ancho, Profundidad, 
    descripcion, stock, imagen_url
) VALUES
(1, 1, 'Sofá de 3 plazas Oslo', 'SAL-001', 
 799.99, 'EUR', true, 
 0.85, 2.20, 0.95, 
 'Sofá escandinavo de 3 plazas tapizado en tela gris...', 
 10, 'https://placehold.co/400x300?text=Sofa+Oslo'),
 
(2, 1, 'Mesa de Centro Nórdica', 'SAL-002', 
 229.99, 'EUR', false, 
 0.45, 1.10, 0.60, 
 'Mesa de centro rectangular en madera de pino...', 
 15, 'https://placehold.co/400x300?text=Mesa+Centro');
-- ... more products

Pedidos Table

Customer orders with auto-generated reference codes.

CREATE TABLE Pedidos (
    pedido_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    usuario_id BIGINT NOT NULL,
    referencia VARCHAR(10) NOT NULL UNIQUE,
    fecha_pedido DATETIME NOT NULL,
    estado VARCHAR(20) NOT NULL,
    
    FOREIGN KEY (usuario_id) REFERENCES Usuario(usuario_id),
    INDEX idx_usuario (usuario_id),
    INDEX idx_estado (estado),
    INDEX idx_fecha (fecha_pedido)
);

Estado values:

PENDIENTE - Order created, awaiting payment
PAGADO - Payment confirmed
ENVIADO - Shipped to customer
ENTREGADO - Delivered
CANCELADO - Cancelled

Auto-generated reference:

@PrePersist
private void generarReferencia() {
    if (this.referencia == null) {
        this.referencia = generarCodigoAleatorio();
    }
}

public static String generarCodigoAleatorio() {
    String caracteres = "ABCDEFGHJKLMNPQRSTUVWXYZ0123456789";
    java.security.SecureRandom random = new java.security.SecureRandom();
    StringBuilder codigo = new StringBuilder(10);
    for (int i = 0; i < 10; i++) {
        codigo.append(caracteres.charAt(random.nextInt(caracteres.length())));
    }
    return codigo.toString();
}

Example reference: A8K3N7Q2M9

Detalle_pedido Table

Order line items (products in an order).

CREATE TABLE Detalle_pedido (
    detalle_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    pedido_id BIGINT NOT NULL,
    producto_id BIGINT NOT NULL,
    cantidad INT NOT NULL,
    precio_unitario DECIMAL(19,2) NOT NULL,
    
    FOREIGN KEY (pedido_id) REFERENCES Pedidos(pedido_id) 
        ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES Producto(producto_id),
    
    INDEX idx_pedido (pedido_id),
    INDEX idx_producto (producto_id)
);

Key features:

Cascade delete - Deleting order deletes all details
Price snapshot - Stores price at time of order (immutable)
Quantity tracking - Number of units ordered

JPA/Hibernate Configuration

DDL Auto Modes

Mode	Description	Use Case
`create`	Drops and recreates tables on startup	Development
`create-drop`	Creates on startup, drops on shutdown	Testing
`update`	Updates schema without data loss	Production
`validate`	Validates schema without changes	Strict production
`none`	No schema management	Manual migrations

Current configuration:

spring.jpa.hibernate.ddl-auto=update

Use update with caution in production. Consider using migration tools like Flyway or Liquibase for production databases.

Naming Strategy

Standard (current):

spring.jpa.hibernate.naming.physical-strategy=
    org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Respects exact entity table/column names:

@Table(name = "Usuario") → Usuario table
@Column(name = "Email") → Email column

Spring Boot default:

spring.jpa.hibernate.naming.physical-strategy=
    org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy

Converts camelCase to snake_case:

Usuario → usuario table
emailValue → email_value column

Data Initialization

data.sql

Executed on startup after Hibernate creates the schema.

-- Categorías de muebles
INSERT IGNORE INTO categorias (categoria_id, nombre, Slug) VALUES
(1, 'Salón',        'salon'),
(2, 'Dormitorio',   'dormitorio'),
(3, 'Cocina',       'cocina'),
(4, 'Baño',         'bano'),
(5, 'Oficina',      'oficina'),
(6, 'Exterior',     'exterior');

-- Productos (17 items)
INSERT IGNORE INTO Producto (
    producto_id, categoria_id, nombre, sku, 
    Cantidad, Moneda, es_destacado, 
    Alto, Ancho, Profundidad, 
    descripcion, stock, imagen_url
) VALUES
-- Salón
(1, 1, 'Sofá de 3 plazas Oslo', 'SAL-001', 
 799.99, 'EUR', true, 0.85, 2.20, 0.95, 
 'Sofá escandinavo de 3 plazas tapizado en tela gris...', 
 10, 'https://placehold.co/400x300?text=Sofa+Oslo'),
 
(2, 1, 'Mesa de Centro Nórdica', 'SAL-002', 
 229.99, 'EUR', false, 0.45, 1.10, 0.60, 
 'Mesa de centro rectangular en madera de pino...', 
 15, 'https://placehold.co/400x300?text=Mesa+Centro'),

-- Dormitorio
(5, 2, 'Cama Matrimonial Zen 150x200', 'DOR-001', 
 649.99, 'EUR', true, 0.90, 1.60, 2.10, 
 'Cama de matrimonio con cabecero tapizado...', 
 12, 'https://placehold.co/400x300?text=Cama+Zen'),

-- More products...

Key features:

INSERT IGNORE - Skips if row already exists (prevents duplicates)
Placeholder images - Using placehold.co
Rich product data - 17 furniture items across 6 categories
Realistic stock levels - 5-30 units per product

Database Queries

Custom Repository Queries

Method name queries:

public interface ProductoRepository extends JpaRepository<Producto, Long> {
    // SELECT * FROM Producto WHERE sku = ?
    Optional<Producto> findBySku(String sku);
    
    // SELECT COUNT(*) FROM Producto WHERE sku = ?
    boolean existsBySku(String sku);
    
    // Complex method name query
    List<Producto> findByNombreContainingIgnoreCaseOrDescripcionContainingIgnoreCase(
        String nombre, String descripcion
    );
    
    // Query embedded value object
    List<Producto> findByPrecioCantidadBetween(
        BigDecimal min, BigDecimal max
    );
}

JPQL queries:

@Query("SELECT p FROM Producto p WHERE p.categoria.categoria_id = :categoriaId")
List<Producto> findByCategoriaId(@Param("categoriaId") Long categoriaId);

@Query("SELECT p FROM Producto p WHERE p.es_destacado = :es_destacado")
List<Producto> findByEs_destacado(@Param("es_destacado") Boolean es_destacado);

Native SQL (if needed):

@Query(value = "SELECT * FROM Producto WHERE MATCH(nombre, descripcion) AGAINST(?1)", 
       nativeQuery = true)
List<Producto> fullTextSearch(String searchTerm);

Performance Optimization

Indexing Strategy

Primary keys:

All tables have AUTO_INCREMENT primary keys
Clustered index for fast lookups

Unique indexes:

Usuario.Email - Fast user lookup by email
Producto.sku - Unique product identifier
Pedidos.referencia - Order tracking

Foreign key indexes:

Producto.categoria_id - Fast category filtering
Pedidos.usuario_id - User order history
Detalle_pedido.pedido_id - Order details lookup

Query optimization indexes:

Producto.es_destacado - Featured products query
Pedidos.estado - Filter by order status
Pedidos.fecha_pedido - Date range queries

Lazy Loading

All relationships use FetchType.LAZY to avoid N+1 query problems:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "categoria_id")
private Categorias categoria;

@OneToMany(mappedBy = "pedido", fetch = FetchType.LAZY)
private List<Detalle_pedido> detalles;

Benefits:

Reduces initial query size
Loads related entities only when accessed
Prevents loading entire object graphs

Ensure transactions are open when accessing lazy-loaded relationships to avoid LazyInitializationException.

Database Maintenance

Backup Strategy

Full backup:

mysqldump -u root -p apiIquea > backup_$(date +%Y%m%d).sql

Restore:

mysql -u root -p apiIquea < backup_20260311.sql

View Schema

Show all tables:

SHOW TABLES;

Describe table structure:

DESCRIBE Producto;
SHOW CREATE TABLE Producto;

View indexes:

SHOW INDEXES FROM Producto;

Common Queries

Count products by category:

SELECT c.nombre, COUNT(p.producto_id) as total
FROM categorias c
LEFT JOIN Producto p ON c.categoria_id = p.categoria_id
GROUP BY c.categoria_id;

Top selling products:

SELECT p.nombre, SUM(dp.cantidad) as total_vendido
FROM Producto p
JOIN Detalle_pedido dp ON p.producto_id = dp.producto_id
GROUP BY p.producto_id
ORDER BY total_vendido DESC
LIMIT 10;

User order history:

SELECT u.username, COUNT(pe.pedido_id) as num_pedidos, 
       SUM(dp.precio_unitario * dp.cantidad) as total_gastado
FROM Usuario u
JOIN Pedidos pe ON u.usuario_id = pe.usuario_id
JOIN Detalle_pedido dp ON pe.pedido_id = dp.pedido_id
GROUP BY u.usuario_id;

Entity-Table Mapping Summary

Entity	Table	Primary Key	Relationships
Usuario	Usuario	usuario_id	→ Pedidos (One-to-Many)
Categorias	categorias	categoria_id	→ Producto (One-to-Many)
Producto	Producto	producto_id	← Categorias (Many-to-One) / → Detalle_pedido (One-to-Many)
Pedido	Pedidos	pedido_id	← Usuario (Many-to-One) / → Detalle_pedido (One-to-Many, cascade)
Detalle_pedido	Detalle_pedido	detalle_id	← Pedido (Many-to-One) / ← Producto (Many-to-One)

Auto Schema Generation

Hibernate creates tables from JPA entities automatically

Data Initialization

data.sql populates initial categories and products on startup

Value Objects

Embedded objects (Precio, Dimensiones) stored as columns

Referential Integrity

Foreign keys enforce relationships between tables

Overview

Getting Started

Frontend Guide

Backend Guide

User Guides

Database Design

Database Design

Database Configuration

application.properties

Database Schema

Entity-Relationship Diagram

Table Definitions

Usuario Table

categorias Table

Producto Table

Pedidos Table

Detalle_pedido Table

JPA/Hibernate Configuration

DDL Auto Modes

Naming Strategy

Data Initialization

data.sql

Database Queries

Custom Repository Queries

Performance Optimization

Indexing Strategy

Lazy Loading

Database Maintenance

Backup Strategy

View Schema

Common Queries

Entity-Table Mapping Summary

Auto Schema Generation

Data Initialization

Value Objects

Referential Integrity

Overview

Getting Started

Frontend Guide

Backend Guide

User Guides

​Database Design

​Database Configuration

​application.properties

​Database Schema

​Entity-Relationship Diagram

​Table Definitions

​Usuario Table

​categorias Table

​Producto Table

​Pedidos Table

​Detalle_pedido Table

​JPA/Hibernate Configuration

​DDL Auto Modes

​Naming Strategy

​Data Initialization

​data.sql

​Database Queries

​Custom Repository Queries

​Performance Optimization

​Indexing Strategy

​Lazy Loading

​Database Maintenance

​Backup Strategy

​View Schema

​Common Queries

​Entity-Table Mapping Summary

Auto Schema Generation

Data Initialization

Value Objects

Referential Integrity

Database Design

Database Configuration

application.properties

Database Schema

Entity-Relationship Diagram

Table Definitions

Usuario Table

categorias Table

Producto Table

Pedidos Table

Detalle_pedido Table

JPA/Hibernate Configuration

DDL Auto Modes

Naming Strategy

Data Initialization

data.sql

Database Queries

Custom Repository Queries

Performance Optimization

Indexing Strategy

Lazy Loading

Database Maintenance

Backup Strategy

View Schema

Common Queries

Entity-Table Mapping Summary